using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using WegasManagementSystem.Models;

namespace WegasManagementSystem.DAL
{
    /// <summary>
    /// 工资数据访问类
    /// </summary>
    public class SalaryDAL
    {
        #region 工资项目管理

        /// <summary>
        /// 添加工资项目
        /// </summary>
        /// <param name="salaryItem">工资项目对象</param>
        /// <returns>新工资项目ID，失败返回0</returns>
        public int AddSalaryItem(SalaryItem salaryItem)
        {
            string sql = @"INSERT INTO sal_salary_item (item_name, item_type, calculation_formula, is_active, display_order, created_time, updated_time) 
                          VALUES (@item_name, @item_type, @calculation_formula, @is_active, @display_order, @created_time, @updated_time);
                          SELECT LAST_INSERT_ID();";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@item_name", salaryItem.ItemName),
                new MySqlParameter("@item_type", salaryItem.ItemType),
                new MySqlParameter("@calculation_formula", salaryItem.CalculationFormula),
                new MySqlParameter("@is_active", salaryItem.IsActive),
                new MySqlParameter("@display_order", salaryItem.DisplayOrder),
                new MySqlParameter("@created_time", salaryItem.CreatedTime),
                new MySqlParameter("@updated_time", salaryItem.UpdatedTime)
            };

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters);
            return result != null ? Convert.ToInt32(result) : 0;
        }

        /// <summary>
        /// 更新工资项目
        /// </summary>
        /// <param name="salaryItem">工资项目对象</param>
        /// <returns>更新成功返回true，失败返回false</returns>
        public bool UpdateSalaryItem(SalaryItem salaryItem)
        {
            string sql = @"UPDATE sal_salary_item 
                          SET item_name = @item_name, item_type = @item_type, 
                              calculation_formula = @calculation_formula, is_active = @is_active, 
                              display_order = @display_order, updated_time = @updated_time
                          WHERE item_id = @item_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@item_id", salaryItem.ItemId),
                new MySqlParameter("@item_name", salaryItem.ItemName),
                new MySqlParameter("@item_type", salaryItem.ItemType),
                new MySqlParameter("@calculation_formula", salaryItem.CalculationFormula),
                new MySqlParameter("@is_active", salaryItem.IsActive),
                new MySqlParameter("@display_order", salaryItem.DisplayOrder),
                new MySqlParameter("@updated_time", DateTime.Now)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除工资项目
        /// </summary>
        /// <param name="itemId">工资项目ID</param>
        /// <returns>删除成功返回true，失败返回false</returns>
        public bool DeleteSalaryItem(int itemId)
        {
            // 检查是否有工资记录使用了该工资项目
            string checkSql = "SELECT COUNT(*) FROM sal_salary_record WHERE item_id = @item_id";
            MySqlParameter checkParam = new MySqlParameter("@item_id", itemId);
            object checkResult = DBConnection.ExecuteScalar(checkSql, CommandType.Text, checkParam);
            
            if (checkResult != null && Convert.ToInt32(checkResult) > 0)
            {
                throw new Exception("该工资项目已有工资记录，无法删除！");
            }

            string sql = "DELETE FROM sal_salary_item WHERE item_id = @item_id";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@item_id", itemId)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 获取所有工资项目
        /// </summary>
        /// <param name="includeInactive">是否包含未启用的项目</param>
        /// <returns>工资项目列表</returns>
        public List<SalaryItem> GetAllSalaryItems(bool includeInactive = false)
        {
            string sql = "SELECT * FROM sal_salary_item";
            if (!includeInactive)
            {
                sql += " WHERE is_active = 1";
            }
            sql += " ORDER BY display_order, item_id";

            DataTable dt = DBConnection.ExecuteDataTable(sql);
            List<SalaryItem> salaryItems = new List<SalaryItem>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    salaryItems.Add(MapRowToSalaryItem(row));
                }
            }

            return salaryItems;
        }

        /// <summary>
        /// 根据ID获取工资项目
        /// </summary>
        /// <param name="itemId">工资项目ID</param>
        /// <returns>工资项目对象</returns>
        public SalaryItem GetSalaryItemById(int itemId)
        {
            string sql = "SELECT * FROM sal_salary_item WHERE item_id = @item_id";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@item_id", itemId)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                return MapRowToSalaryItem(dt.Rows[0]);
            }
            return null;
        }

        /// <summary>
        /// 检查工资项目名称是否存在
        /// </summary>
        /// <param name="itemName">工资项目名称</param>
        /// <param name="excludeItemId">排除的工资项目ID</param>
        /// <returns>存在返回true，不存在返回false</returns>
        public bool CheckItemNameExists(string itemName, int excludeItemId = 0)
        {
            string sql = "SELECT COUNT(*) FROM sal_salary_item WHERE item_name = @item_name";
            List<MySqlParameter> parameters = new List<MySqlParameter>
            {
                new MySqlParameter("@item_name", itemName)
            };

            if (excludeItemId > 0)
            {
                sql += " AND item_id <> @item_id";
                parameters.Add(new MySqlParameter("@item_id", excludeItemId));
            }

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters.ToArray());
            return result != null && Convert.ToInt32(result) > 0;
        }

        /// <summary>
        /// 获取工资项目类型列表
        /// </summary>
        /// <returns>工资项目类型列表</returns>
        public List<string> GetSalaryItemTypes()
        {
            // 返回预定义的工资项目类型
            return new List<string> { "income", "deduction" };
        }

        #endregion

        #region 工资记录管理

        /// <summary>
        /// 添加工资记录
        /// </summary>
        /// <param name="salaryRecord">工资记录对象</param>
        /// <returns>新记录ID，失败返回0</returns>
        public long AddSalaryRecord(SalaryRecord salaryRecord)
        {
            string sql = @"INSERT INTO sal_salary_record (emp_id, item_id, year_month, amount, created_time, updated_time) 
                          VALUES (@emp_id, @item_id, @year_month, @amount, @created_time, @updated_time);
                          SELECT LAST_INSERT_ID();";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", salaryRecord.EmpId),
                new MySqlParameter("@item_id", salaryRecord.ItemId),
                new MySqlParameter("@year_month", salaryRecord.YearMonth),
                new MySqlParameter("@amount", salaryRecord.Amount),
                new MySqlParameter("@created_time", salaryRecord.CreatedTime),
                new MySqlParameter("@updated_time", salaryRecord.UpdatedTime)
            };

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters);
            return result != null ? Convert.ToInt64(result) : 0;
        }

        /// <summary>
        /// 批量添加工资记录
        /// </summary>
        /// <param name="salaryRecords">工资记录列表</param>
        /// <returns>成功添加的记录数</returns>
        public int BatchAddSalaryRecords(List<SalaryRecord> salaryRecords)
        {
            int successCount = 0;
            using (MySqlConnection connection = DBConnection.GetConnection())
            {
                if (DBConnection.OpenConnection(connection))
                {
                    using (MySqlTransaction transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            string sql = @"INSERT INTO sal_salary_record (emp_id, item_id, year_month, amount, created_time, updated_time) 
                                          VALUES (@emp_id, @item_id, @year_month, @amount, @created_time, @updated_time)";

                            using (MySqlCommand cmd = new MySqlCommand(sql, connection, transaction))
                            {
                                MySqlParameter empIdParam = new MySqlParameter("@emp_id", MySqlDbType.Int32);
                                MySqlParameter itemIdParam = new MySqlParameter("@item_id", MySqlDbType.Int32);
                                MySqlParameter yearMonthParam = new MySqlParameter("@year_month", MySqlDbType.VarChar, 7);
                                MySqlParameter amountParam = new MySqlParameter("@amount", MySqlDbType.Decimal);
                                MySqlParameter createdTimeParam = new MySqlParameter("@created_time", MySqlDbType.DateTime);
                                MySqlParameter updatedTimeParam = new MySqlParameter("@updated_time", MySqlDbType.DateTime);

                                cmd.Parameters.Add(empIdParam);
                                cmd.Parameters.Add(itemIdParam);
                                cmd.Parameters.Add(yearMonthParam);
                                cmd.Parameters.Add(amountParam);
                                cmd.Parameters.Add(createdTimeParam);
                                cmd.Parameters.Add(updatedTimeParam);

                                foreach (SalaryRecord record in salaryRecords)
                                {
                                    empIdParam.Value = record.EmpId;
                                    itemIdParam.Value = record.ItemId;
                                    yearMonthParam.Value = record.YearMonth;
                                    amountParam.Value = record.Amount;
                                    createdTimeParam.Value = record.CreatedTime;
                                    updatedTimeParam.Value = record.UpdatedTime;

                                    successCount += cmd.ExecuteNonQuery();
                                }
                            }

                            transaction.Commit();
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
            }
            return successCount;
        }

        /// <summary>
        /// 更新工资记录
        /// </summary>
        /// <param name="salaryRecord">工资记录对象</param>
        /// <returns>更新成功返回true，失败返回false</returns>
        public bool UpdateSalaryRecord(SalaryRecord salaryRecord)
        {
            string sql = @"UPDATE sal_salary_record 
                          SET amount = @amount, updated_time = @updated_time
                          WHERE record_id = @record_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@record_id", salaryRecord.RecordId),
                new MySqlParameter("@amount", salaryRecord.Amount),
                new MySqlParameter("@updated_time", DateTime.Now)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除工资记录
        /// </summary>
        /// <param name="recordId">记录ID</param>
        /// <returns>删除成功返回true，失败返回false</returns>
        public bool DeleteSalaryRecord(long recordId)
        {
            string sql = "DELETE FROM sal_salary_record WHERE record_id = @record_id";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@record_id", recordId)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除员工某月的所有工资记录
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <param name="yearMonth">年月(YYYY-MM)</param>
        /// <returns>删除成功返回true，失败返回false</returns>
        public bool DeleteEmployeeSalaryRecords(int empId, string yearMonth)
        {
            string sql = "DELETE FROM sal_salary_record WHERE emp_id = @emp_id AND year_month = @year_month";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", empId),
                new MySqlParameter("@year_month", yearMonth)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 获取员工某月的工资记录
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <param name="yearMonth">年月(YYYY-MM)</param>
        /// <returns>工资记录列表</returns>
        public List<SalaryRecord> GetEmployeeSalaryRecords(int empId, string yearMonth)
        {
            string sql = @"SELECT r.*, i.item_name, i.item_type, e.name as emp_name, d.dept_name
                          FROM sal_salary_record r
                          JOIN sal_salary_item i ON r.item_id = i.item_id
                          JOIN emp_employee e ON r.emp_id = e.emp_id
                          LEFT JOIN org_department d ON e.dept_id = d.dept_id
                          WHERE r.emp_id = @emp_id AND r.year_month = @year_month
                          ORDER BY i.display_order, i.item_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", empId),
                new MySqlParameter("@year_month", yearMonth)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            List<SalaryRecord> salaryRecords = new List<SalaryRecord>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    salaryRecords.Add(MapRowToSalaryRecord(row));
                }
            }

            return salaryRecords;
        }

        /// <summary>
        /// 获取某月所有员工的工资记录
        /// </summary>
        /// <param name="yearMonth">年月(YYYY-MM)</param>
        /// <returns>工资记录列表</returns>
        public List<SalaryRecord> GetMonthlySalaryRecords(string yearMonth)
        {
            string sql = @"SELECT r.*, i.item_name, i.item_type, e.name as emp_name, d.dept_name
                          FROM sal_salary_record r
                          JOIN sal_salary_item i ON r.item_id = i.item_id
                          JOIN emp_employee e ON r.emp_id = e.emp_id
                          LEFT JOIN org_department d ON e.dept_id = d.dept_id
                          WHERE r.year_month = @year_month
                          ORDER BY e.dept_id, e.emp_id, i.display_order, i.item_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@year_month", yearMonth)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            List<SalaryRecord> salaryRecords = new List<SalaryRecord>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    salaryRecords.Add(MapRowToSalaryRecord(row));
                }
            }

            return salaryRecords;
        }

        /// <summary>
        /// 获取某部门某月的工资记录
        /// </summary>
        /// <param name="deptId">部门ID</param>
        /// <param name="yearMonth">年月(YYYY-MM)</param>
        /// <returns>工资记录列表</returns>
        public List<SalaryRecord> GetDepartmentSalaryRecords(int deptId, string yearMonth)
        {
            string sql = @"SELECT r.*, i.item_name, i.item_type, e.name as emp_name, d.dept_name
                          FROM sal_salary_record r
                          JOIN sal_salary_item i ON r.item_id = i.item_id
                          JOIN emp_employee e ON r.emp_id = e.emp_id
                          LEFT JOIN org_department d ON e.dept_id = d.dept_id
                          WHERE e.dept_id = @dept_id AND r.year_month = @year_month
                          ORDER BY e.emp_id, i.display_order, i.item_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@dept_id", deptId),
                new MySqlParameter("@year_month", yearMonth)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            List<SalaryRecord> salaryRecords = new List<SalaryRecord>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    salaryRecords.Add(MapRowToSalaryRecord(row));
                }
            }

            return salaryRecords;
        }

        /// <summary>
        /// 获取员工的历史工资记录
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <returns>工资记录列表，按年月倒序排列</returns>
        public List<SalaryRecord> GetEmployeeHistorySalaryRecords(int empId)
        {
            string sql = @"SELECT r.*, i.item_name, i.item_type, e.name as emp_name, d.dept_name
                          FROM sal_salary_record r
                          JOIN sal_salary_item i ON r.item_id = i.item_id
                          JOIN emp_employee e ON r.emp_id = e.emp_id
                          LEFT JOIN org_department d ON e.dept_id = d.dept_id
                          WHERE r.emp_id = @emp_id
                          ORDER BY r.year_month DESC, i.display_order, i.item_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", empId)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            List<SalaryRecord> salaryRecords = new List<SalaryRecord>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    salaryRecords.Add(MapRowToSalaryRecord(row));
                }
            }

            return salaryRecords;
        }

        /// <summary>
        /// 获取可用的年月列表
        /// </summary>
        /// <returns>年月字符串列表</returns>
        public List<string> GetAvailableYearMonths()
        {
            string sql = "SELECT DISTINCT year_month FROM sal_salary_record ORDER BY year_month DESC";
            DataTable dt = DBConnection.ExecuteDataTable(sql);
            List<string> yearMonths = new List<string>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    yearMonths.Add(row["year_month"].ToString());
                }
            }

            return yearMonths;
        }

        /// <summary>
        /// 检查指定员工某月的工资记录是否存在
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <param name="yearMonth">年月 (YYYY-MM)</param>
        /// <returns>是否存在</returns>
        public bool CheckSalaryExists(int empId, string yearMonth)
        {
            string sql = "SELECT COUNT(*) FROM sal_salary_record WHERE emp_id = @emp_id AND year_month = @year_month";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", empId),
                new MySqlParameter("@year_month", yearMonth)
            };

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters);
            return Convert.ToInt32(result) > 0;
        }

        /// <summary>
        /// 获取指定员工某月的工资记录
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <param name="yearMonth">年月 (YYYY-MM)</param>
        /// <returns>工资记录列表</returns>
        public List<SalaryRecord> GetSalaryByEmployeeAndMonth(int empId, string yearMonth)
        {
            return GetEmployeeSalaryRecords(empId, yearMonth);
        }

        /// <summary>
        /// 获取指定月份的所有工资记录
        /// </summary>
        /// <param name="yearMonth">年月 (YYYY-MM)</param>
        /// <returns>工资记录列表</returns>
        public List<SalaryRecord> GetSalaryByMonth(string yearMonth)
        {
            string sql = @"SELECT sr.*, si.item_name, si.item_type, e.emp_name, d.dept_name 
                           FROM salary_record sr
                           JOIN salary_item si ON sr.item_id = si.item_id
                           JOIN employee e ON sr.emp_id = e.emp_id
                           JOIN department d ON e.dept_id = d.dept_id
                           WHERE sr.year_month = @year_month
                           ORDER BY sr.emp_id, si.item_type DESC, si.item_id";
            
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@year_month", yearMonth)
            };
            
            try
            {
                DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
                List<SalaryRecord> records = new List<SalaryRecord>();
                
                foreach (DataRow row in dt.Rows)
                {
                    records.Add(MapRowToSalaryRecord(row));
                }
                
                return records;
            }
            catch (Exception ex)
            {
                throw new Exception($"获取{yearMonth}工资记录失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 保存工资记录
        /// </summary>
        /// <param name="salaryRecord">工资记录</param>
        /// <returns>是否保存成功</returns>
        public bool SaveSalary(SalaryRecord salaryRecord)
        {
            if (salaryRecord.RecordId > 0)
            {
                // 更新现有记录
                return UpdateSalaryRecord(salaryRecord);
            }
            else
            {
                // 添加新记录
                return AddSalaryRecord(salaryRecord) > 0;
            }
        }

        /// <summary>
        /// 确认工资发放
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <param name="yearMonth">年月 (YYYY-MM)</param>
        /// <param name="confirmDate">确认日期</param>
        /// <returns>是否确认成功</returns>
        public bool ConfirmSalaryPayment(int empId, string yearMonth, DateTime confirmDate)
        {
            string sql = @"UPDATE sal_salary_record SET payment_status = 1, payment_date = @payment_date, 
                         updated_time = @updated_time WHERE emp_id = @emp_id AND year_month = @year_month";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", empId),
                new MySqlParameter("@year_month", yearMonth),
                new MySqlParameter("@payment_date", confirmDate),
                new MySqlParameter("@updated_time", DateTime.Now)
            };

            int rowsAffected = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 获取指定部门、指定月份的工资记录
        /// </summary>
        /// <param name="deptId">部门ID，0表示所有部门</param>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns>工资记录列表</returns>
        /// <remarks>
        /// 注意：确保WegasManagementSystem.Models.SalaryInfo类已正确定义并可被此类引用
        /// </remarks>
        public List<SalaryInfo> GetSalaryByMonth(int deptId, int year, int month)
        {
            // 构造年月字符串 YYYY-MM
            string yearMonth = $"{year}-{month:D2}";
            
            List<SalaryInfo> salaryList = new List<SalaryInfo>();
            
            try
            {
                // 根据部门ID获取工资记录
                List<SalaryRecord> records;
                if (deptId <= 0)
                {
                    // 获取所有部门的工资记录
                    records = GetMonthlySalaryRecords(yearMonth);
                }
                else
                {
                    // 获取指定部门的工资记录
                    records = GetDepartmentSalaryRecords(deptId, yearMonth);
                }
                
                if (records == null || records.Count == 0)
                {
                    return salaryList;
                }
                
                // 按员工ID分组汇总工资记录
                Dictionary<int, List<SalaryRecord>> empRecords = new Dictionary<int, List<SalaryRecord>>();
                foreach (var record in records)
                {
                    if (!empRecords.ContainsKey(record.EmpId))
                    {
                        empRecords[record.EmpId] = new List<SalaryRecord>();
                    }
                    empRecords[record.EmpId].Add(record);
                }
                
                // 为每个员工计算工资汇总
                foreach (var kvp in empRecords)
                {
                    int empId = kvp.Key;
                    List<SalaryRecord> empSalaryRecords = kvp.Value;
                    
                    if (empSalaryRecords.Count == 0)
                        continue;
                    
                    // 创建工资信息对象
                    SalaryInfo salaryInfo = new SalaryInfo
                    {
                        EmpId = empId,
                        EmpName = empSalaryRecords[0].EmpName,
                        DeptName = empSalaryRecords[0].DeptName,
                        Year = year,
                        Month = month,
                        BaseSalary = 0,
                        PositionAllowance = 0,
                        PerformanceBonus = 0,
                        OvertimePay = 0,
                        SocialInsurance = 0,
                        HousingFund = 0,
                        IncomeTax = 0,
                        AbsenceDeduction = 0,
                        OtherDeductions = 0,
                        TotalIncome = 0,
                        TotalDeduction = 0,
                        NetSalary = 0
                    };
                    
                    // 汇总各项工资
                    foreach (var record in empSalaryRecords)
                    {
                        if (record.ItemType == "income")
                        {
                            salaryInfo.TotalIncome += record.Amount;
                            
                            // 根据工资项名称设置具体收入项
                            switch (record.ItemName)
                            {
                                case "基本工资":
                                    salaryInfo.BaseSalary = record.Amount;
                                    break;
                                case "岗位津贴":
                                    salaryInfo.PositionAllowance = record.Amount;
                                    break;
                                case "绩效奖金":
                                    salaryInfo.PerformanceBonus = record.Amount;
                                    break;
                                case "加班工资":
                                    salaryInfo.OvertimePay = record.Amount;
                                    break;
                            }
                        }
                        else if (record.ItemType == "deduction")
                        {
                            salaryInfo.TotalDeduction += record.Amount;
                            
                            // 根据工资项名称设置具体扣款项
                            switch (record.ItemName)
                            {
                                case "社会保险":
                                    salaryInfo.SocialInsurance = record.Amount;
                                    break;
                                case "住房公积金":
                                    salaryInfo.HousingFund = record.Amount;
                                    break;
                                case "个人所得税":
                                    salaryInfo.IncomeTax = record.Amount;
                                    break;
                                case "缺勤扣款":
                                    salaryInfo.AbsenceDeduction = record.Amount;
                                    break;
                                case "其他扣款":
                                    salaryInfo.OtherDeductions = record.Amount;
                                    break;
                            }
                        }
                    }
                    
                    // 计算实发工资
                    salaryInfo.NetSalary = salaryInfo.TotalIncome - salaryInfo.TotalDeduction;
                    
                    // 添加到结果列表
                    salaryList.Add(salaryInfo);
                }
                
                return salaryList;
            }
            catch (Exception ex)
            {
                LogError("获取部门工资记录失败", ex);
                throw new Exception("获取部门工资记录失败: " + ex.Message);
            }
        }
        
        /// <summary>
        /// 记录错误日志
        /// </summary>
        private void LogError(string message, Exception ex)
        {
            // 此处可实现错误日志记录
            System.Diagnostics.Debug.WriteLine($"错误: {message}, 详情: {ex.Message}");
        }

        /// <summary>
        /// 更新工资条
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <param name="yearMonth">年月</param>
        /// <param name="paid">是否已支付</param>
        /// <returns>更新结果</returns>
        public bool UpdateSalaryPayment(int empId, string yearMonth, bool paid)
        {
            string sql = @"UPDATE salary_payment SET paid = @paid, updated_time = @updated_time WHERE emp_id = @emp_id AND year_month = @year_month";
            
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", empId),
                new MySqlParameter("@year_month", yearMonth),
                new MySqlParameter("@paid", paid),
                new MySqlParameter("@updated_time", DateTime.Now)
            };
            
            try
            {
                return DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters) > 0;
            }
            catch (Exception ex)
            {
                throw new Exception("更新工资条支付状态失败", ex);
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        /// 将DataRow映射到SalaryItem对象
        /// </summary>
        /// <param name="row">DataRow</param>
        /// <returns>SalaryItem对象</returns>
        private SalaryItem MapRowToSalaryItem(DataRow row)
        {
            SalaryItem salaryItem = new SalaryItem();
            salaryItem.ItemId = Convert.ToInt32(row["item_id"]);
            salaryItem.ItemName = row["item_name"].ToString();
            salaryItem.ItemType = row["item_type"].ToString();
            
            if (row["calculation_formula"] != DBNull.Value)
                salaryItem.CalculationFormula = row["calculation_formula"].ToString();
            
            salaryItem.IsActive = Convert.ToInt32(row["is_active"]);
            salaryItem.DisplayOrder = Convert.ToInt32(row["display_order"]);
            salaryItem.CreatedTime = Convert.ToDateTime(row["created_time"]);
            salaryItem.UpdatedTime = Convert.ToDateTime(row["updated_time"]);

            return salaryItem;
        }

        /// <summary>
        /// 将DataRow映射到SalaryRecord对象
        /// </summary>
        /// <param name="row">DataRow</param>
        /// <returns>SalaryRecord对象</returns>
        private SalaryRecord MapRowToSalaryRecord(DataRow row)
        {
            SalaryRecord salaryRecord = new SalaryRecord();
            salaryRecord.RecordId = Convert.ToInt64(row["record_id"]);
            salaryRecord.EmpId = Convert.ToInt32(row["emp_id"]);
            salaryRecord.ItemId = Convert.ToInt32(row["item_id"]);
            salaryRecord.YearMonth = row["year_month"].ToString();
            salaryRecord.Amount = Convert.ToDecimal(row["amount"]);
            salaryRecord.CreatedTime = Convert.ToDateTime(row["created_time"]);
            salaryRecord.UpdatedTime = Convert.ToDateTime(row["updated_time"]);

            // 可能是连接查询的额外字段
            if (row.Table.Columns.Contains("item_name") && row["item_name"] != DBNull.Value)
                salaryRecord.ItemName = row["item_name"].ToString();

            if (row.Table.Columns.Contains("item_type") && row["item_type"] != DBNull.Value)
                salaryRecord.ItemType = row["item_type"].ToString();

            if (row.Table.Columns.Contains("emp_name") && row["emp_name"] != DBNull.Value)
                salaryRecord.EmpName = row["emp_name"].ToString();

            if (row.Table.Columns.Contains("dept_name") && row["dept_name"] != DBNull.Value)
                salaryRecord.DeptName = row["dept_name"].ToString();

            return salaryRecord;
        }

        #endregion
    }
} 